package com.power.dao.impl;

import com.power.dao.SovleQueryDao;
import com.power.pojo.SovlePojo;
import com.power.utils.JdbcBuild;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: 朱雄
 * Date: 2020/10/10 11:05
 * Description:
 * Version: V1.0
 */
public class SovleQueryDaoImpl extends JdbcBuild implements SovleQueryDao {
    @Override
    public List<Map<String, Object>> sovleQueryAll(int newPage, int newLimit) throws SQLException {
        String sql="SELECT\n" +
                "\tsovle.sovle_task,\n" +
                "\tsovle.lssue_time,\n" +
                "\tdefecttype.defect_name,\n" +
                "\tdefect_details.*, defect_grade.defect_grade_name,\n" +
                "\twork_order.work_order_des,\n" +
                "\tstates.state_name,\n" +
                "\ta.user_name AS find_name,\n" +
                "\tb.user_name AS lssue_name\n" +
                "FROM\n" +
                "\tsovle,\n" +
                "\tdefecttype,\n" +
                "\tdefect_details,\n" +
                "\tdefect_grade,\n" +
                "\tstates,\n" +
                "\twork_order,\n" +
                "\tusers AS a,\n" +
                "\tusers AS b\n" +
                "WHERE\n" +
                "\tsovle.defect_details_id = defect_details.defect_id\n" +
                "AND \n" +
                " defect_details.defect_id = defecttype.defect_id\n" +
                "AND \n" +
                "sovle.sovle_state = states.state_id\n" +
                "AND \n" +
                "sovle.sovle_account=a.user_account\n" +
                "AND\n" +
                "sovle.user_id=b.user_account\n" +
                "AND\n" +
                "sovle.work_id=work_order.work_id\n" +
                "AND\n" +
                "defect_details.defect_grade=defect_grade.defect_id limit ?,?";
        Object[] params={(newPage-1)*newLimit,newLimit};
        List<Map<String, Object>> list=super.findAllForList(sql,params);
        return list;
    }

    @Override
    public int sovleCount() throws SQLException {
        String sql="SELECT\n" +
                "\tcount(*)\n" +
                "FROM\n" +
                "\tsovle,\n" +
                "\tdefecttype,\n" +
                "\tdefect_details,\n" +
                "\tdefect_grade,\n" +
                "\tstates,\n" +
                "\twork_order,\n" +
                "\tusers AS a,\n" +
                "\tusers AS b\n" +
                "WHERE\n" +
                "\tsovle.defect_details_id = defect_details.defect_id\n" +
                "AND \n" +
                " defect_details.defect_id = defecttype.defect_id\n" +
                "AND \n" +
                "sovle.sovle_state = states.state_id\n" +
                "AND \n" +
                "sovle.sovle_account=a.user_account\n" +
                "AND\n" +
                "sovle.user_id=b.user_account\n" +
                "AND\n" +
                "sovle.work_id=work_order.work_id\n" +
                "AND\n" +
                "defect_details.defect_grade=defect_grade.defect_id";

        int count=super.findAllForInt(sql,null);
        return count;
    }


    @Override
    public List<Map<String, Object>> sovleQueryByAll(SovlePojo sovlePojo, int page, int limit) throws SQLException {
        String sql="SELECT\n" +
                "\tsovle.sovle_task,\n" +
                "\tsovle.lssue_time,\n" +
                "\tdefecttype.defect_name,\n" +
                "\tdefect_details.*, defect_grade.defect_grade_name,\n" +
                "\twork_order.work_order_des,\n" +
                "\tstates.state_name,\n" +
                "\ta.user_name AS find_name,\n" +
                "\tb.user_name AS lssue_name\n" +
                "FROM\n" +
                "\tsovle,\n" +
                "\tdefecttype,\n" +
                "\tdefect_details,\n" +
                "\tdefect_grade,\n" +
                "\tstates,\n" +
                "\twork_order,\n" +
                "\tusers AS a,\n" +
                "\tusers AS b\n" +
                "WHERE\n" +
                "\tsovle.defect_details_id = defect_details.defect_id\n" +
                "AND \n" +
                " defect_details.defect_id = defecttype.defect_id\n " +
                "AND \n" +
                " sovle.sovle_state = states.state_id\n" +
                "AND \n" +
                " sovle.sovle_account=a.user_account\n" +
                "AND\n" +
                " sovle.user_id=b.user_account\n" +
                "AND\n" +
                " sovle.work_id=work_order.work_id\n" +
                "AND\n" +
                " defect_details.defect_grade=defect_grade.defect_id ";
        List<Object> list=new ArrayList();
        if (sovlePojo.getSovle_task() !=null && !"".equals(sovlePojo.getSovle_task())){
            sql+=" and sovle_task=? ";
            list.add(sovlePojo.getSovle_task());
        }
        if (sovlePojo.getSovle_state() !=null && !"".equals(sovlePojo.getSovle_state())){
            sql+=" and sovle_state=? ";
            list.add(sovlePojo.getSovle_state());
        }
        if (sovlePojo.getDefect_grade() !=null && !"".equals(sovlePojo.getDefect_grade())){
            sql+=" and defect_grade.defect_id=? ";
            list.add(sovlePojo.getDefect_grade());
        }
        if (sovlePojo.getWork_id() != null && !"".equals(sovlePojo.getWork_id())){
            sql+=" and work_order.work_id=? ";
            list.add(sovlePojo.getWork_id());
        }
        if (sovlePojo.getDefect_id() !=null && !"".equals(sovlePojo.getDefect_id())){
            sql+=" and defecttype.defect_id=? ";
            list.add(sovlePojo.getDefect_id());
        }
        if ((sovlePojo.getStart_time() !=null && !"".equals(sovlePojo.getStart_time()))
                && (sovlePojo.getEnd_time() ==null || "".equals(sovlePojo.getEnd_time()))){
            sql+=" and sovle.lssue_time BETWEEN ? AND NOW() ";
            list.add(sovlePojo.getStart_time());
        }
        if ((sovlePojo.getStart_time() !=null && !"".equals(sovlePojo.getStart_time())) && (sovlePojo.getEnd_time() !=null && !"".equals(sovlePojo.getEnd_time()))){
            sql+=" and sovle.lssue_time  BETWEEN ? AND ?";
            list.add(sovlePojo.getStart_time());
            list.add(sovlePojo.getEnd_time());
        }
        sql+="limit ?,?";
        list.add(page);
        list.add(limit);
        Object[] object=list.toArray();
        System.out.println(list);
        List<Map<String,Object>> listAll=super.findAllForList(sql,object);
        return listAll;
    }

    @Override
    public int sovleByAllCount(SovlePojo sovlePojo) throws SQLException {
        String sql="SELECT\n" +
                "\tcount(*)\n" +
                "FROM\n" +
                "\tsovle,\n" +
                "\tdefecttype,\n" +
                "\tdefect_details,\n" +
                "\tdefect_grade,\n" +
                "\tstates,\n" +
                "\twork_order,\n" +
                "\tusers AS a,\n" +
                "\tusers AS b\n" +
                "WHERE\n" +
                "\tsovle.defect_details_id = defect_details.defect_id\n" +
                "AND \n" +
                " defect_details.defect_id = defecttype.defect_id\n" +
                "AND \n" +
                "sovle.sovle_state = states.state_id\n" +
                "AND \n" +
                "sovle.sovle_account=a.user_account\n" +
                "AND\n" +
                "sovle.user_id=b.user_account\n" +
                "AND\n" +
                "sovle.work_id=work_order.work_id\n" +
                "AND\n" +
                "defect_details.defect_grade=defect_grade.defect_id";
        List<Object> list=new ArrayList();
        if (sovlePojo.getSovle_task() !=null && !"".equals(sovlePojo.getSovle_task())){
            sql+=" and sovle_task=? ";
            list.add(sovlePojo.getSovle_task());
        }
        if (sovlePojo.getSovle_state() !=null && !"".equals(sovlePojo.getSovle_state())){
            sql+=" and sovle_state=? ";
            list.add(sovlePojo.getSovle_state());
        }
        if (sovlePojo.getDefect_grade() !=null && !"".equals(sovlePojo.getDefect_grade())){
            sql+=" and defect_grade.defect_id=? ";
            list.add(sovlePojo.getDefect_grade());
        }
        if (sovlePojo.getWork_id() != null && !"".equals(sovlePojo.getWork_id())){
            sql+=" and work_order.work_id=? ";
            list.add(sovlePojo.getWork_id());
        }
        if (sovlePojo.getDefect_id() !=null && !"".equals(sovlePojo.getDefect_id())){
            sql+=" and defecttype.defect_id=? ";
            list.add(sovlePojo.getDefect_id());
        }
        if ((sovlePojo.getStart_time() !=null && !"".equals(sovlePojo.getStart_time()))
                && (sovlePojo.getEnd_time() ==null || "".equals(sovlePojo.getEnd_time()))){
            sql+=" and sovle.lssue_time BETWEEN ? AND NOW() ";
            list.add(sovlePojo.getStart_time());
        }
        if ((sovlePojo.getStart_time() !=null && !"".equals(sovlePojo.getStart_time()))
                && (sovlePojo.getEnd_time() !=null || !"".equals(sovlePojo.getEnd_time()))){
            sql+=" and sovle.lssue_time BETWEEN ? AND ?";
            list.add(sovlePojo.getStart_time());
            list.add(sovlePojo.getEnd_time());
        }
        Object[] object=list.toArray();
        int res=super.findAllForInt(sql,object);
        return res;
    }
}
